package org.example.Dao;

import org.example.Pojo.Mobile;
import org.example.Utils.DbUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @PROJECT_NAME: mobile
 * @DESCRIPTION:
 * @USER: 韩冰
 * @DATE: 2022/10/13 0013 8:59
 */
public class MobileDao {

    //查看全部
    public List<Mobile> selectAll() {
        Connection connection = null;
        List<Mobile> list = new ArrayList<>();
        try {
            connection = DbUtils.getConnection();
            String sql = "select * from tb_mobile";
            ResultSet resultSet = connection.prepareStatement(sql).executeQuery();
            while (resultSet.next()) {
                Mobile mobile = new Mobile();
                mobile.setId(resultSet.getInt("id"));
                mobile.setBrand(resultSet.getString("Brand"));
                mobile.setModel(resultSet.getString("Model"));
                mobile.setPrice(resultSet.getDouble("Price"));
                mobile.setCount(resultSet.getInt("Count"));
                mobile.setVersion(resultSet.getString("Version"));
                list.add(mobile);
            }

            resultSet.close();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.closeConn(connection);
        }
        return list;
    }


    //根据ID查询
    public Mobile selectById(int id) {
        Connection connection = null;
        Mobile mobile = new Mobile();
        try {
            connection = DbUtils.getConnection();
            String sql = "select * from tb_mobile where id = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                mobile.setId(resultSet.getInt("id"));
                mobile.setBrand(resultSet.getString("Brand"));
                mobile.setModel(resultSet.getString("Model"));
                mobile.setPrice(resultSet.getDouble("Price"));
                mobile.setCount(resultSet.getInt("Count"));
                mobile.setVersion(resultSet.getString("Version"));
            }

            resultSet.close();
            preparedStatement.close();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.closeConn(connection);
        }
        return mobile;
    }

    //根据品牌查询
    public Mobile selectByBrand(String brand) {
        Connection connection = null;
        Mobile mobile = new Mobile();
        try {
            connection = DbUtils.getConnection();
            String sql = "select * from tb_mobile where id = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, brand);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                mobile.setId(resultSet.getInt("id"));
                mobile.setBrand(resultSet.getString("Brand"));
                mobile.setModel(resultSet.getString("Model"));
                mobile.setPrice(resultSet.getDouble("Price"));
                mobile.setCount(resultSet.getInt("Count"));
                mobile.setVersion(resultSet.getString("Version"));
            }

            resultSet.close();
            preparedStatement.close();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.closeConn(connection);
        }
        return mobile;
    }


    //根据价格范围查询
    public List<Mobile> selectBetweenPrice(double a, double b) {
        Connection connection = null;
        List<Mobile> list = new ArrayList<>();
        Mobile mobile = new Mobile();
        try {
            connection = DbUtils.getConnection();
            String sql = "SELECT * from tb_mobile where Price BETWEEN  ? AND ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setDouble(1, a);
            preparedStatement.setDouble(2, b);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                mobile.setId(resultSet.getInt("id"));
                mobile.setBrand(resultSet.getString("Brand"));
                mobile.setModel(resultSet.getString("Model"));
                mobile.setPrice(resultSet.getDouble("Price"));
                mobile.setCount(resultSet.getInt("Count"));
                mobile.setVersion(resultSet.getString("Version"));
                list.add(mobile);
            }

            resultSet.close();
            preparedStatement.close();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.closeConn(connection);
        }
        return list;
    }

    //完成信息录入的功能
    public boolean insert(Mobile b) {
        Connection connection = null;
        int count = 0;
        try {
            connection = DbUtils.getConnection();

            connection.setAutoCommit(false);
            String sql = "INSERT INTO `shixun01`.`tb_mobile`(`Brand`, `Model`, `Price`, `Count`, `Version`) " +
                    "VALUES (?, ?, ?, ?, ?);";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, b.getBrand());
            preparedStatement.setString(2, b.getModel());
            preparedStatement.setDouble(3, b.getPrice());
            preparedStatement.setInt(4, b.getCount());
            preparedStatement.setString(5, b.getVersion());


            count += preparedStatement.executeUpdate();
            connection.commit();
            preparedStatement.close();
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        } finally {
            DbUtils.closeConn(connection);
        }
        return count >= 1;
    }

    //完成删除的功能；若删除的手机编号不存在，给出对应提示
    public boolean deleteById(int id) {
        Connection connection = null;
        int affectRow = 0;
        try {
            connection = DbUtils.getConnection();
            connection.setAutoCommit(false);
            String sql = "delete  from `tb_mobile` where id = ?;";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            affectRow = preparedStatement.executeUpdate();
            connection.commit();
            preparedStatement.close();
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        } finally {
            DbUtils.closeConn(connection);
        }
        return affectRow >= 1;
    }

    //完成根据手机品牌查询手机信息，要求使用模糊查询。
    public List<Mobile> like(String brand) {
        Connection connection = null;
        List<Mobile> list = new ArrayList<>();
        Mobile mobile = new Mobile();
        try {
            connection = DbUtils.getConnection();
            String sql = "SELECT * from tb_mobile where Brand like ?\"%\"";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, brand);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                mobile.setId(resultSet.getInt("id"));
                mobile.setBrand(resultSet.getString("Brand"));
                mobile.setModel(resultSet.getString("Model"));
                mobile.setPrice(resultSet.getDouble("Price"));
                mobile.setCount(resultSet.getInt("Count"));
                mobile.setVersion(resultSet.getString("Version"));
                list.add(mobile);
            }

            resultSet.close();
            preparedStatement.close();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.closeConn(connection);
        }
        return list;
    }

    //完成根据手机编号修改手机价格功能
    public boolean updateById(int id, double price) {
        Connection connection = null;
        int count = 0;
        try {
            connection = DbUtils.getConnection();
            connection.setAutoCommit(false);
            connection.setAutoCommit(false);
            String sql = "UPDATE `shixun01`.`tb_mobile` " +
                    "SET `Price` = ? WHERE `ID` = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setDouble(1, price);
            preparedStatement.setInt(2, id);
            count = preparedStatement.executeUpdate();
            connection.commit();
            preparedStatement.close();
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        } finally {
            DbUtils.closeConn(connection);
        }
        return count >= 1;
    }

}

